from fastapi import FastAPI, Depends, HTTPException,File, UploadFile
from fastapi_offline import FastAPIOffline 
from fastapi.responses import StreamingResponse
import pandas as pd
from io import BytesIO
from database import engine, get_db
from models import Base
from schemas import StudentCreate, StudentResp,OptionResp, OptionKey, CheckIsRightRsp, CheckIsRightUpdate
from sqlalchemy import update
from sqlalchemy.orm import Session
from crud import create_student, get_all_students,get_option_value, incr_option_value, zero_option_value,get_all_check, update_isright
import uvicorn
from fastapi.middleware.cors import CORSMiddleware
from fastapi.routing import APIRoute

from models import Student as StudentORM

Base.metadata.create_all(bind=engine)

app = FastAPIOffline(title="StudentService",
    version="1.0.0",
    docs_url="/docs",      # 可选
    )

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],      # 也可指定 ["http://localhost:8080"] 等
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

# 避免与 crud 函数同名，加 api_ 前缀
@app.get("/isRight", response_model=list[CheckIsRightRsp], operation_id="get_all_check_get_1", tags=["CheckAPI"])
def get_all_check_get_1(db: Session = Depends(get_db)):
    return get_all_check(db)

@app.post("/checkIsRight", response_model=list[CheckIsRightRsp],operation_id="update_check_post_1",  tags=["CheckAPI"])
def update_check_post_1(body: CheckIsRightUpdate, db: Session = Depends(get_db)):
    update_isright(db, body.stuid, body.isright)
    return get_all_check(db)


@app.post("/add_student", response_model=StudentResp)
def add_student(stu: StudentCreate, db=Depends(get_db)):
    return create_student(db, stu)

@app.get("/students", response_model=list[StudentResp])
def list_students(db=Depends(get_db)):
    return get_all_students(db)

@app.get("/download")
def download_excel(db=Depends(get_db)):
    rows = get_all_students(db)
    if not rows:
        raise HTTPException(404, "暂无数据")
    # 整理数据并套用中文列顺序
    data = [
        {
            "序号": r.id,
            "姓名": r.name,
            "性别": r.gender,
            "身高": r.height,
            "体重": r.weight,
            "爱好": r.hobby,
        }
        for r in rows
    ]

    df = pd.DataFrame(data, columns=["序号", "姓名", "性别", "身高", "体重", "爱好"])

    out = BytesIO()
    with pd.ExcelWriter(out, engine="openpyxl") as writer:
        df.to_excel(writer, index=False, sheet_name="students")

    out.seek(0)
    return StreamingResponse(
        out,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=data.xlsx"},
    )

@app.get("/option/{opkey}", response_model=OptionResp)
def read_option(opkey: str, db: Session = Depends(get_db)):
    val = get_option_value(db, opkey)
    if val is None:
        raise HTTPException(status_code=404, detail="opkey not found")
    return {"opkey": opkey, "opvalue": val}

@app.post("/option/incr", response_model=OptionResp)
def incr_option(body: OptionKey, db: Session = Depends(get_db)):
    new_val = incr_option_value(db, body.opkey)
    return {"opkey": body.opkey, "opvalue": new_val}

@app.post("/option/zero", response_model=OptionResp)
def zero_option(body: OptionKey, db: Session = Depends(get_db)):
    new_val = zero_option_value(db, body.opkey)
    return {"opkey": body.opkey, "opvalue": new_val}

@app.post("/resultsUpload", response_model=list[StudentResp])   # StudentResp 之前已定义
def results_upload(file: UploadFile = File(...), db: Session = Depends(get_db)):
    """接收 Excel → 按 id 更新学生信息 → 返回更新后全量数据"""
    # 1. 校验文件类型
    if file.content_type not in ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                                 "application/vnd.ms-excel"):
        raise HTTPException(400, "请上传 Excel 文件（.xlsx/.xls）")

    # 2. 读 Excel
    try:
        df = pd.read_excel(BytesIO(file.file.read()), engine="openpyxl" if file.filename.endswith("xlsx") else "xlrd")
    except Exception as e:
        raise HTTPException(400, f"Excel 解析失败：{e}")

    # 3. 列名校正（允许中英文混写）
    col_map = {
        "id": ["id", "序号"],
        "name": ["name", "姓名"],
        "gender": ["gender", "性别"],
        "height": ["height", "身高"],
        "weight": ["weight", "体重"],
        "hobby": ["hobby", "爱好"],
    }
    # 统一小写
    df.columns = [str(c).strip() for c in df.columns]
    rename = {}
    for field, aliases in col_map.items():
        for a in aliases:
            if a in df.columns:
                rename[a] = field
                break
    if not rename:
        raise HTTPException(400, "Excel 缺少必要列（id/序号 等）")
    df = df.rename(columns=rename)

    # 4. 逐行更新
    updated_ids = []
    for _, row in df.iterrows():
        if pd.isna(row.get("id")):
            continue
        try:
            stu_id = int(row["id"])
        except ValueError:
            continue

        # 构造更新字典，只保留非空字段
        upd = {}
        for f in ("name", "gender", "height", "weight", "hobby"):
            if f in row and not pd.isna(row[f]):
                upd[f] = str(row[f]).strip() if f in {"name", "gender", "hobby"} else row[f]
        if not upd:
            continue

        stmt = update(StudentORM).where(StudentORM.id == stu_id).values(**upd)
        res = db.execute(stmt)
        if res.rowcount:          # 实际更新到才记录
            updated_ids.append(stu_id)

    db.commit()

    # 5. 返回更新后的全部数据
    final_rows = db.query(StudentORM).all()
    return final_rows


if __name__ == "__main__":
    uvicorn.run("main:app", host="0.0.0.0", port=18000, reload=True)